package com.audi.box.adapter;

import java.util.ArrayList;

import com.audi.dto.PlaceItem;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import android.util.Log;

/**
 * 
 * @author NamNT17/HuyPVA - Modify by CuongPH7
 * 
 */
public class SQLiteAsusHelperPlace extends SQLiteAsusHelperCommon {
    /**
     * Class constructor specifying Context to create
     * 
     * @param context
     *            android.content.Context
     */
    public SQLiteAsusHelperPlace(Context context) {
        super(context);
        // TODO Auto-generated constructor stub
    }
    
    /**
     * Add a PlaceItem
     * 
     * @param place
     * @return 1 if (successful, 0 if (Place exist
     */
    public int addPlace(PlaceItem place) {
        Log.d("HuySQLite Log", "addPlace");
        int id = 0;
        
        ContentValues values = convertPlaceItemToContentValue(place);
        
        id = sqlInsert(TABLE_PLACE, null, values);
        
        // add data to history
        // updateHistoryPlace(id, 1, 0); HuyPVA-
        return id;
    }
    
    /**
     * Update a PlaceItem
     * 
     * @param place
     * @return 1 if (successful, 0 if (Place does not exist
     */
    public int updatePlace(PlaceItem place) {
        Log.d("HuySQLite Log", "updatePlace");
        int id = 0;
        
        ContentValues values = convertPlaceItemToContentValue(place);
        
        id = sqlUpdate(TABLE_PLACE, values, KEY_PLACE_ID + " = ?",
                new String[] {String.valueOf(place.getPlaceId())});
        
        // add data to history
        // updateHistoryPlace(id, 1, 0); HuyPVA-
        return id;
    }
    
    /**
     * @deprecated {@link #updatePlace(PlaceItem)}
     * @param placeItem
     * @return 1 if (successful, 0 if (Place does not exist
     */
    @Deprecated
    public int renamePlace(PlaceItem placeItem) {
        Log.d("HuySQLite Log", "renamePlace");
        
        /*
         * try { HistoryRenamePlaceOrChangeCapability item2 = new
         * HistoryRenamePlaceOrChangeCapability();
         * item2.setRenameFrom(getPlaceItembyId(placeItem.getPlaceId())
         * .getPlaceName()); item2.setRenameTo(placeItem.getPlaceName());
         * item2.setMemberId(2); item2.setPlaceId(placeItem.getPlaceId());
         * item2.setTimeAdd(String.valueOf(System.currentTimeMillis())); //
         * addHistoryRenameOrChangeCapabilityOfPlace(item2); HuyPVA- } catch
         * (Exception e) { }
         */
        
        ContentValues values = new ContentValues();
        values.put(KEY_PLACE_NAME, placeItem.getPlaceName());
        
        return sqlUpdate(TABLE_PLACE, values,
                KEY_PLACE_ID + "=" + String.valueOf(placeItem.getPlaceId()),
                null);
    }
    
    /**
     * Change Place capability
     * 
     * @deprecated {@link #updatePlace(PlaceItem)}
     * @param item
     * @return 1 if (successful, 0 if (Place does not exist
     */
    @Deprecated
    public int changeCapabilityPlace(PlaceItem item) {
        Log.d("HuySQLite Log", "changeCapabilityPlace");
        
        /*
         * // HistoryRenamePlaceOrChangeCapability item1 = new
         * HistoryRenamePlaceOrChangeCapability(); //
         * item1.setCapabilityFrom(getPlaceItembyId(item.getPlaceId()) //
         * .getCapabilityType()); //
         * item1.setCapabilityTo(item.getCapabilityType()); //
         * item1.setMemberId(2); // item1.setPlaceId(item.getPlaceId()); //
         * item1.setTimeAdd(String.valueOf(System.currentTimeMillis())); //
         * addHistoryRenameOrChangeCapabilityOfPlace(item1); HuyPVA-
         */
        
        ContentValues values = new ContentValues();
        values.put(KEY_PLACE_CAPABILITY_TYPE, item.getCapabilityType());
        
        return sqlUpdate(TABLE_PLACE, values,
                KEY_PLACE_ID + "=" + String.valueOf(item.getPlaceId()), null);
    }
    
    // get place id NamNT16
    /**
     * @deprecated {@link #getPlacebyId(int)}
     * @param item
     * @return
     */
    @Deprecated
    public int getPlaceId(PlaceItem item) {
        Log.d("HuySQLite Log", "getPlaceId");
        String selectQuery = "SELECT " + KEY_PLACE_ID + " FROM " + TABLE_PLACE
                + " WHERE " + KEY_PLACE_NAME + "='" + item.getPlaceName()
                + "' AND " + KEY_PLACE_IS_DELETED + " = 0";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        try {
            if (cursorSqliteAsusHelper.getCount() > 0) {
                if (cursorSqliteAsusHelper.moveToFirst()) {
                    return Integer
                            .parseInt(cursorSqliteAsusHelper.getString(0));
                }
            }
        } finally {
            if (cursorSqliteAsusHelper != null) {
                cursorSqliteAsusHelper.close();
            }
            
            if (db != null) {
                db.close();
            }
        }
        
        return 0;
        
    }
    
    /**
     * Get Place Item by Place Id
     * 
     * @param placeId
     *            Place Id
     * @return PlaceItem
     */
    public PlaceItem getPlacebyId(int placeId) {
        Log.d("HuySQLite Log", "getPlacebyId");
        PlaceItem placeItem = null;
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            String selectQuery = "SELECT * FROM " + TABLE_PLACE + " WHERE "
                    + KEY_PLACE_ID + "='" + placeId + "' AND "
                    + KEY_PLACE_IS_DELETED + " = 0";
            db = this.getReadableDatabase();
            cursor = db.rawQuery(selectQuery, null);
            if ((cursor.getCount() > 0) && (cursor.moveToFirst())) {
                placeItem = createPlaceItem(cursor);
            }
            
        } catch (SQLException e) {
            Log.e("DATABASE getPlaceItembyId",
                    "getPlaceItembyId : " + e.toString());
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            
            if (db != null) {
                db.close();
            }
        }
        return placeItem;
    }
    
    // get list device - use in smart_trigger Namnt16 ver1
    // get place id NamNT16
    /**
     * @author NamNT16
     * @deprecated {@link #getPlacebyId(int)}
     * @param placeId
     * @return PlaceItem
     */
    @Deprecated
    public PlaceItem getPlaceItembyId(int placeId) {
        Log.d("HuySQLite Log", "getPlaceItembyId");
        /*
         * PlaceItem vRet = null; try {
         * 
         * String selectQuery = "SELECT * FROM " + TABLE_PLACE + " WHERE " +
         * KEY_PLACE_ID + "='" + placeId + "' AND " + KEY_PLACE_IS_DELETED +
         * " = 0"; // add data SQLiteDatabase db = this.getReadableDatabase();
         * Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null); if
         * (cursorSqliteAsusHelper.getCount() > 0) { if
         * (cursorSqliteAsusHelper.moveToFirst()) {
         * 
         * vRet = new PlaceItem(); // add
         * vRet.setPlaceId(Integer.parseInt(cursorSqliteAsusHelper
         * .getString(0)));
         * vRet.setPlaceName(cursorSqliteAsusHelper.getString(1));
         * vRet.setPlaceImage(cursorSqliteAsusHelper.getString(2));
         * vRet.setCapabilityType(Integer
         * .parseInt(cursorSqliteAsusHelper.getString(3)));
         * 
         * } }
         * 
         * } catch (SQLException e) { Log.e("getPlaceItembyId",
         * "getPlaceItembyId : " + e.toString()); }
         * 
         * return vRet;
         */
        return getPlacebyId(placeId);
        
    }
    
    /**
     * Get Place Item List By Device Id
     * 
     * @return an ArrayList Place Item
     */
    public ArrayList<PlaceItem> getListPlace(int deviceId) {
        Log.d("HuySQLite Log", "getListPlace(int deviceId)");
        ArrayList<PlaceItem> lstPlace = new ArrayList<PlaceItem>();
        /*
         * String selectQuery = "SELECT " + TABLE_PLACE + "." + KEY_PLACE_ID +
         * ", " + TABLE_PLACE + "." + KEY_PLACE_NAME + ", " + TABLE_PLACE + "."
         * + KEY_PLACE_ICON + ", " + TABLE_PLACE + "." +
         * KEY_PLACE_CAPABILITY_TYPE + "," + " (select count(" + TABLE_DEVICE +
         * "." + KEY_DEVICE_ID + ") from " + TABLE_DEVICE + " where " +
         * TABLE_DEVICE + "." + KEY_DEVICE_PLACE_ID + "=" + TABLE_PLACE + "." +
         * KEY_PLACE_ID + " and " + TABLE_DEVICE + "." + KEY_DEVICE_IS_DELETED +
         * "=0" + ") as DEVICE_NUM" + " FROM " + TABLE_PLACE;
         * 
         * selectQuery += " WHERE " + TABLE_PLACE + "." + KEY_PLACE_IS_DELETED +
         * "=0"; selectQuery += " ORDER BY " + TABLE_PLACE + "." + KEY_PLACE_ID
         * + " DESC";
         */
        String selectQuery = "SELECT " + TABLE_PLACE + "." + KEY_PLACE_ID
                + ", " + TABLE_PLACE + "." + KEY_PLACE_NAME + ", "
                + TABLE_PLACE + "." + KEY_PLACE_ICON + ", " + TABLE_PLACE + "."
                + KEY_PLACE_CAPABILITY_TYPE + "," + " COUNT(" + TABLE_DEVICE
                + "." + KEY_DEVICE_ID + ") as DEVICE_NUM"
                
                + " FROM " + TABLE_PLACE + " LEFT JOIN " + TABLE_DEVICE
                + " ON " + TABLE_PLACE + "." + KEY_PLACE_ID + "="
                + TABLE_DEVICE + "." + KEY_DEVICE_PLACE_ID;
        
        ArrayList<String> where = new ArrayList<String>();
        
        where.add(TABLE_PLACE + "." + KEY_PLACE_IS_DELETED + "=0");
        
        if (deviceId != 0) {
            where.add(TABLE_DEVICE + "." + KEY_DEVICE_ID + "=" + deviceId);
        }
        
        selectQuery += " WHERE " + TextUtils.join(" AND ", where);
        
        selectQuery += " GROUP BY " + TABLE_PLACE + "." + KEY_PLACE_ID;
        selectQuery += " ORDER BY " + TABLE_PLACE + "." + KEY_PLACE_ID
                + " DESC";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(selectQuery, null);
            if (cursor.moveToFirst()) {
                do {
                    
                    PlaceItem place = createPlaceItem(cursor);
                    
                    int columnIndex = cursor
                            .getColumnIndexOrThrow("DEVICE_NUM");
                    if ((columnIndex != -1) && (!cursor.isNull(columnIndex))) {
                        place.setDeviceNumber(cursor.getInt(columnIndex));
                    }
                    
                    lstPlace.add(place);
                    
                } while (cursor.moveToNext());
            }
        } catch (SQLException e) {
            Log.e("DATABASE getListPlace", e.toString());
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return lstPlace;
    }
    
    /**
     * Get all Place Item List
     * 
     * @return an ArrayList Place Item
     */
    public ArrayList<PlaceItem> getListPlace() {
        Log.d("HuySQLite Log", "getListPlace()");
        return getListPlace(0);
    }
    
    /**
     * Get all Place Item
     * 
     * @return an ArrayList Place Item
     */
    public PlaceItem getPlaceItemByDevice(int deviceId) {
        Log.d("HuySQLite Log", "getPlaceItemByDevice");
        ArrayList<PlaceItem> lstPlace = getListPlace(0);
        if (lstPlace.size() > 0) {
            return lstPlace.get(0);
        } else {
            return new PlaceItem();
        }
    }
    
    public String getNumberPlaceInRootAccess() {
        Log.d("HuySQLite Log", "getNumberPlaceInRootAccess");
        String strCountDevice = null;
        String selectQuery = "SELECT " + "COUNT (" + TABLE_PLACE + "."
                + KEY_PLACE_ID + ") as DEVICE_NUM" + " FROM " + TABLE_PLACE;
        // where device and place is not deleted
        selectQuery += " WHERE " + TABLE_PLACE + "." + KEY_PLACE_IS_DELETED
                + " = 0 ";
        selectQuery += " AND " + TABLE_PLACE + "." + KEY_PLACE_ID + " IN ("
                + " SELECT " + TABLE_DEVICE + "." + KEY_DEVICE_PLACE_ID
                + " FROM " + TABLE_DEVICE + ")";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        try {
            if (cursorSqliteAsusHelper.moveToFirst()) {
                do {
                    strCountDevice = cursorSqliteAsusHelper.getString(0);
                    
                } while (cursorSqliteAsusHelper.moveToNext());
            }
        } catch (SQLException e) {
        } finally {
            if (cursorSqliteAsusHelper != null) {
                cursorSqliteAsusHelper.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return strCountDevice;
        
    }
    
    public PlaceItem getInfoOfPlace(int placeID) {
        Log.d("HuySQLite Log", "getInfoOfPlace");
        PlaceItem place = new PlaceItem();
        String selectQuery = "SELECT " + TABLE_PLACE + "." + KEY_PLACE_ID
                + ", " + TABLE_PLACE + "." + KEY_PLACE_NAME + ", "
                + TABLE_PLACE + "." + KEY_PLACE_ICON + ", " + TABLE_PLACE + "."
                + KEY_PLACE_CAPABILITY_TYPE + "," + " (select count("
                + TABLE_DEVICE + "." + KEY_DEVICE_ID + ") from " + TABLE_DEVICE
                + " where " + TABLE_DEVICE + "." + KEY_DEVICE_PLACE_ID + "="
                + TABLE_PLACE + "." + KEY_PLACE_ID + " and " + TABLE_DEVICE
                + "." + KEY_DEVICE_IS_DELETED + " =0" + ") as DEVICE_NUM" + ","
                + TABLE_HISTORY_PLACE + "." + KEY_HISTORY_PLACE_DATETIME
                + " FROM " + TABLE_PLACE + " LEFT OUTER JOIN "
                + TABLE_HISTORY_PLACE + " ON " + TABLE_HISTORY_PLACE + "."
                + KEY_HISTORY_PLACE_PLACE_ID + " = " + TABLE_PLACE + "."
                + KEY_PLACE_ID;
        selectQuery += " WHERE " + TABLE_PLACE + "." + KEY_PLACE_ID + " = "
                + placeID;
        selectQuery += " ORDER BY " + TABLE_PLACE + "." + KEY_PLACE_ID
                + " DESC";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        try {
            if (cursor.moveToFirst()) {
                do {
                    int columnIndex = 0;
                    
                    PlaceItem placeItem = createPlaceItem(cursor);
                    
                    columnIndex = cursor.getColumnIndexOrThrow("DEVICE_NUM");
                    if (!cursor.isNull(columnIndex)) {
                        placeItem.setDeviceNumber(cursor.getInt(columnIndex));
                    }
                    
                    // WHY USE DATETIME FROM HISTORY
                    columnIndex = cursor
                            .getColumnIndexOrThrow(KEY_HISTORY_PLACE_DATETIME);
                    if (!cursor.isNull(columnIndex)) {
                        place.setDateCreated(cursor.getString(columnIndex));
                    }
                    
                } while (cursor.moveToNext());
            }
            return place;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
    }
    
    /**
     * Check Place Item Exist
     * 
     * @param place
     *            PlaceItem
     * @return <code>true</code> if PlaceItem Exist <code>false</code> if
     *         PlaceItem does not exist
     */
    public boolean checkExistPlace(PlaceItem place) {
        Log.d("HuySQLite Log", "checkExistPlace");
        String selectQuery = "SELECT " + KEY_PLACE_ID + " FROM " + TABLE_PLACE
                + " WHERE " + KEY_PLACE_ID + "='" + place.getPlaceId()
                + "' AND " + KEY_PLACE_IS_DELETED + "=0";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if (cursorSqliteAsusHelper.getCount() > 0) {
            return true;
        }
        return false;
    }
    
    /**
     * Check PlaceItem's Name Exist
     * 
     * @param place
     *            PlaceItem
     * @return <code>true</code> if PlaceItem Exist <code>false</code> if
     *         PlaceItem does not exist
     */
    public boolean checkExistPlaceByName(PlaceItem item) {
        Log.d("HuySQLite Log", "checkExistPlace");
        String selectQuery = "SELECT " + KEY_PLACE_ID + " FROM " + TABLE_PLACE
                + " WHERE " + KEY_PLACE_NAME + "='" + item.getPlaceName()
                + "' AND " + KEY_PLACE_ID + " <> "
                + ((item.getPlaceId() == null) ? 0 : item.getPlaceId())
                + " AND " + KEY_PLACE_IS_DELETED + "=0";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursorSqliteAsusHelper = db.rawQuery(selectQuery, null);
        if (cursorSqliteAsusHelper.getCount() > 0) {
            return true;
        }
        return false;
    }
    
    /**
     * Delete a Place Item
     * 
     * @param place
     * @return 1 if (successful, 0 if (Place does not exist
     */
    public int deletePlace(PlaceItem place) {
        Log.d("HuySQLite Log", "deletePlace");
        ContentValues values = new ContentValues();
        values.put(KEY_PLACE_IS_DELETED, 1);
        
        return sqlUpdate(TABLE_PLACE, values,
                KEY_PLACE_ID + "=" + String.valueOf(place.getPlaceId()), null);
    }
    
    /**
     * Delete a Place by placeId
     * 
     * @param placeId
     * @deprecated <code>removePlace(int placeId)</code>
     * @see removePlace(int placeId)
     */
    @Deprecated
    public void deletePlace(int placeId) {
        Log.d("HuySQLite Log", "deletePlace");
        /*
         * CuongPH7 comment this block, just set deleted field to 1
         * 
         * String sqlCommand = "DELETE FROM " + TABLE_PLACE + " WHERE " +
         * KEY_PLACE_ID + "=" + placeId; SQLiteDatabase db =
         * this.getWritableDatabase();
         * 
         * // update history
         * 
         * // -------------------- db.execSQL(sqlCommand);
         */
    }
    
    /**
     * Delete a Place by placeId
     * 
     * @deprecated {@link #deletePlace(int)}
     * @param placeId
     *            Place Id
     * @return the number of rows affected
     */
    @Deprecated
    public int removePlace(int placeId) {
        return 0;
        /*
         * SQLiteDatabase db = this.getWritableDatabase(); ContentValues values
         * = new ContentValues(); values.put(KEY_PLACE_IS_DELETED, 1);
         * 
         * return sqlUpdate(TABLE_PLACE, values, KEY_PLACE_ID + "=" +
         * String.valueOf(placeId), null);
         */
    }
    
    /*
     * update place_id for device with old place_id
     */
    
    public boolean updatePlaceToUncategory(int oldplaceId, int checkIsDelete) {
        Log.d("HuySQLite Log", "updatePlaceToUncategory");
        try {
            String sql = "UPDATE " + TABLE_PLACE + " SET "
                    + KEY_PLACE_IS_DELETED + " = " + checkIsDelete + " WHERE "
                    + KEY_PLACE_ID + " = " + oldplaceId;
            SQLiteDatabase db = this.getWritableDatabase();
            db.execSQL(sql);
            
            db.close();
            // update history
            
            // updateHistoryPlace(oldplaceId, 1, 1); HuyPVA-
            
            return true;
        } catch (SQLException e) {
            Log.e("updatePlaceToUncategory",
                    "updatePlaceToUncategory " + e.toString());
        }
        return false;
        
    }
    
}
